package com.lsh.dao;

import com.lsh.domain.Article;
import com.lsh.domain.ArticlePage;
import com.lsh.domain.UserDatum;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * 文章dao类（UserArticle实体类）
 */
@Repository
public interface ArticleDao {

    //获取多条文章记录
    @Select("SELECT a.id, DATE_FORMAT(a.createtime,'%Y年%m月%d日') as createtime, a.title," +
            " a.content_html, i.nickname, u.username, h.URL, IFNULL(c.num, 0) AS comnum" +
            " FROM user_articles a" +
            " LEFT OUTER JOIN (SELECT uid, nickname FROM user_informations) i ON a.uid=i.uid" +
            " LEFT OUTER JOIN (SELECT uid, URL FROM user_headportrait) h ON a.uid = h.uid" +
            " LEFT OUTER JOIN (SELECT artid, COUNT(artid) AS num FROM user_comments GROUP BY artid) c ON a.id = c.artid" +
            " LEFT OUTER JOIN (SELECT id, username FROM users) u ON a.uid = u.id" +
            " WHERE draft = 1;")
    @Results(value = {
            @Result(id = true, column = "id", property = "art_id"),
            @Result(column = "createtime", property = "create_time", javaType = String.class),
            @Result(column = "title", property = "art_title"),
            @Result(column = "content_html", property = "art_content"),
            @Result(column = "nickname", property = "art_nickname"),
            @Result(column = "username", property = "art_username"),
            @Result(column = "URL", property = "art_headurl"),
            @Result(column = "comnum", property = "art_comnum")
    })
    List<LinkedHashMap<String, Object>> getArticleList();

    //查询当前作者该文章的上一条文章
    @Select("SELECT a.id AS aid, a.title, u.username FROM user_articles a" +
            " LEFT JOIN users u ON a.uid = u.id" +
            " WHERE a.id =" +
            " (SELECT max(id) FROM user_articles WHERE id < #{aid} AND uid = #{uid})")
    LinkedHashMap<String, Object> getArticlePrev(@Param("uid") Integer userId, @Param("aid") String artId);

    //查询当前作者该文章的下一条文章
    @Select("SELECT a.id AS aid, a.title, u.username FROM user_articles a" +
            " LEFT JOIN users u ON a.uid = u.id" +
            " WHERE a.id =" +
            " (SELECT min(id) FROM  user_articles WHERE id > #{aid} AND uid = #{uid})")
    LinkedHashMap<String, Object> getArticleNext(@Param("uid") Integer userId, @Param("aid") String artId);


    //获取文章表信息以及用户头像（联表）
    @Select("SELECT a.id as art_id, a.title as title, a.content_md as content, h.URL as head_url" +
            " FROM user_articles a " +
            "LEFT JOIN user_headportrait h ON a.uid = h.uid " +
            "WHERE h.uid = #{userId} AND a.id = #{artId}")
    Map<String, Object> getArticleToHead(@Param("userId") int userId, @Param("artId") int artId);

    //获取指定文章详细信息
    @Select("SELECT a.id as art_id, a.uid as uid, h.URL as art_headurl, i.nickname as art_nickname, IFNULL(c.comment_num, 0) as comment_num," +
            " DATE_FORMAT(a.createtime,'%Y年%m月%d日 %H:%i:%s') as create_time, a.title as art_title, a.content_html as art_content" +
            " FROM user_articles a" +
            " LEFT JOIN (SELECT id FROM users WHERE username=#{username}) u ON a.uid = u.id" +
            " LEFT JOIN (SELECT uid, nickname FROM user_informations) i ON a.uid = i.uid" +
            " LEFT JOIN (SELECT uid, URL FROM user_headportrait) h ON a.uid = h.uid" +
            " LEFT JOIN (SELECT artid, COUNT(id) AS comment_num FROM user_comments WHERE artid = ${artId}) c ON a.id = c.artid" +
            " WHERE a.id=#{artId};")
    Map<String, Object> getArticleDetail(@Param("username") String username, @Param("artId") String artId);

    //保存文章（draft值：0为草稿，1为发布）
    @Insert("INSERT INTO user_articles (title, content_md, content_html,draft, uid) " +
            "VALUES (#{title}, #{contentMd}, #{contentHtml}, #{draft}, #{uid});")
    @Options(useGeneratedKeys = true, keyProperty = "artId", keyColumn = "id")
    int saveArticle(Article article);

    //修改文章
    @Update("UPDATE user_articles SET createtime=CURRENT_TIMESTAMP, title=#{title}, " +
            "content_md=#{contentMd}, content_html=#{contentHtml}, draft=#{draft} WHERE id=#{artId};")
    int updateArticle(Article article);

    //获取文章年份列表
    @Select("SELECT DISTINCT DATE_FORMAT(createtime,'%Y') AS year " +
            "FROM user_articles" +
            " WHERE uid = #{userId} AND draft = 1 " +
            "ORDER BY year ASC;")
    List<String> getArticleYear(@Param("userId") String userId);

    //获取文章月份列表
    @Select("SELECT DISTINCT DATE_FORMAT(createtime,'%m') AS month" +
            " FROM user_articles" +
            " WHERE uid = #{userId} AND draft = 1" +
            " ORDER BY month ASC;")
    List<String> getArticleMonth(@Param("userId") String userId);

    //查询文章记录数-发布的文章
    @Select("<script>" +
            "SELECT COUNT(id) as art_count" +
            " FROM user_articles" +
            " WHERE uid = #{userId} and draft = #{draft} " +
            "<if test='ap.year != null'>" +
            " AND DATE_FORMAT(createtime,'%Y') = ${ap.year}" +
            "</if>" +
            "<if test='ap.month != null'>" +
            " AND DATE_FORMAT(createtime,'%m') = ${ap.month}" +
            "</if>" +
            "<if test='ap.keyword != null and ap.keyword != \"\"'>" +
            "AND title LIKE '%${ap.keyword}%'" +
            "</if>" +
            "</script>")
        int getAllArticleCount(@Param("userId") String userId, @Param("draft") int draft, @Param("ap") ArticlePage ap);

    //查询文章记录数-草稿箱
    @Select("SELECT COUNT(id) as art_count" +
            " FROM user_articles" +
            " WHERE uid = #{userId} and draft = #{draft}")
    int getDraftsArticleCount(@Param("userId") String userId, @Param("draft") int draft);

    //获取文章分页内容-发布的文章
    @Select("<script>" +
            "SELECT u.id as art_id, u.title as art_title, " +
            "DATE_FORMAT(u.createtime,'%Y年%m月%d日 %H:%i:%s') as create_time, IFNULL(c.com_num, 0) as com_num " +
            "FROM user_articles u " +
            "LEFT JOIN (SELECT artid, COUNT(artid) as com_num FROM user_comments GROUP BY artid) c ON u.id = c.artid " +
            "WHERE uid = #{userId} AND draft = 1 " +
            "<if test='ap.year != null'>" +
            " AND DATE_FORMAT(u.createtime,'%Y') = ${ap.year}" +
            "</if>" +
            "<if test='ap.month != null'>" +
            " AND DATE_FORMAT(u.createtime,'%m') = ${ap.month}" +
            "</if>" +
            "<if test='ap.keyword != null and ap.keyword != \"\"'>" +
            " AND u.title LIKE '%${ap.keyword}%'" +
            "</if>" +
            "ORDER BY create_time DESC " +
            "LIMIT #{lim}, #{row}" +
            "</script>")
    List<LinkedHashMap<String, Object>> getAllArticlePage(@Param("userId") String userId, @Param("lim") int lim, @Param("row") int row, @Param("ap") ArticlePage ap);

    //删除文章
    @Delete("DELETE FROM user_articles " +
            "WHERE id = #{artId} AND uid = #{userId} AND draft = #{draft}")
    int deleteArticle(@Param("artId") String artId, @Param("userId") String userId, @Param("draft") int draft);

    //获取文章分页内容-草稿箱
    @Select("SELECT u.id as art_id, u.title as art_title, " +
            "DATE_FORMAT(u.createtime,'%Y年%m月%d日 %H:%i:%s') as create_time, IFNULL(c.com_num, 0) as com_num " +
            "FROM user_articles u " +
            "LEFT JOIN (SELECT artid, COUNT(artid) as com_num FROM user_comments GROUP BY artid) c ON u.id = c.artid " +
            "WHERE uid = #{userId} AND draft = 0 " +
            "ORDER BY create_time DESC " +
            "LIMIT #{lim}, #{row}"
            )
    List<LinkedHashMap<String,Object>> getDraftsArticlePage(@Param("userId")String userId, @Param("lim") int lim, @Param("row") int row);
}
